package persistencia;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import modelo.Proveedor;

public class AdmPersistenciaProveedor {
	public static AdmPersistenciaProveedor instancia;
	
	public static AdmPersistenciaProveedor getInstancia(){
		if(instancia==null)
			instancia = new AdmPersistenciaProveedor();
			return instancia;
	}

	public Proveedor selectProveedor(String cuit) {
		
		Connection con = PoolConnection.getPoolConnection().getConnection();
		try {
			Proveedor proveedor = null;
			String senten = "select * from zzpracPrevioProve.dbo.proveedores where cuit = ?";
			PreparedStatement ps = con.prepareStatement(senten);
			ps.setString(1, cuit);
			ResultSet rs = ps.executeQuery();
			while (rs.next()){
				proveedor = new Proveedor();
				proveedor.setCuit(rs.getString(1));
				proveedor.setNombre(rs.getString(2));
			}
			PoolConnection.getPoolConnection().releaseConnection(con);
			return proveedor;
			
		} catch (SQLException e) {
				System.out.println("Error en insert proveedor");
		}
		return null;
	}

	public void insertProveedor(String cuit, String nombre) {
		Connection con = PoolConnection.getPoolConnection().getConnection();
		try {
			String senten = "insert into zzpracPrevioProve.dbo.proveedores values (?,?)";
			PreparedStatement ps = con.prepareStatement(senten);
			ps.setString(1, cuit);
			ps.setString(2, nombre);
			ps.execute();
			PoolConnection.getPoolConnection().releaseConnection(con);
						
		} catch (SQLException e) {
			System.out.println("error en insert proveedor");;
		}		
	}

	public void insertProducto(String cuit, int codigo, String des) {
		Connection con = PoolConnection.getPoolConnection().getConnection();
		
		try {
			String senten = "insert into zzpracPrevioProve.dbo.productos values (?,?,?)";
			PreparedStatement ps = con.prepareStatement(senten);
			ps.setString(1, cuit);
			ps.setInt(2, codigo);
			ps.setString(3, des);
			ps.execute();
			
		} catch (SQLException e) {
			System.out.println("error en insert producto");
		}
	}

	public String listarProductos(String cuit) {
		Connection con = PoolConnection.getPoolConnection().getConnection();
		try {
			String datos = "CUIT: "+cuit+"\n";
			String senten = "select*from zzpracPrevioProve.dbo.productos where cuit like ?";
			PreparedStatement ps = con.prepareStatement(senten);
			ps.setString(1, cuit);
			ResultSet rs = ps.executeQuery();
			while (rs.next()){
				datos += rs.getString(3)+"\n";
			}
			PoolConnection.getPoolConnection().releaseConnection(con);
			return datos;
		} catch (SQLException e) {
			System.out.println("error en listar productos");
		}
		return null;
		
	}

	public String listarCantProd(String cuit) {
		Connection con = PoolConnection.getPoolConnection().getConnection();
		try {
			String datos=null;
			String senten = "select count(cuit) from zzpracPrevioProve.dbo.productos where cuit like ? group by cuit";
			PreparedStatement ps = con.prepareStatement(senten);
			ps.setString(1, cuit);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				datos = Integer.toString(rs.getInt(1));
			}
			PoolConnection.getPoolConnection().releaseConnection(con);
			return datos;
			
		} catch (SQLException e) {
			System.out.println("error en listar cant productos");
		}return null;
		
	}
	
	
	
}
